package com.shq.ex1.bus.util;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class UtilMethod {
	private static SQLiteDatabase db = null;
	private static List<String> list = null;
	
 	public static List<String> getXianByZhan(String databasePath,String zhan){//根据站点查询线路
		list = new ArrayList<String>();
		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
		String sql = "select cnbusw.busw from cnbus,cnbusw where cnbus.xid = cnbusw.id and cnbus.zhan = ? and cnbus.kind = 1 order by  shuzi ;";
		
		Cursor cursor = db.rawQuery(sql, new String[]{zhan});
		while(cursor.moveToNext()){
			String busw=  cursor.getString(cursor.getColumnIndex("busw"));
			list.add(busw);
		}
		db.close();
		return list;
	}
 	
 	public static List<String> getZhanByXianGo(String databasePath,String xian){//根据线路查询站点（去程）
 		list = new ArrayList<String>();
		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
		String sql = "select cnbus.zhan,pm from cnbus,cnbusw where cnbus.xid  = cnbusw.id and cnbusw.busw = ? and cnbus.kind = 1 order by  pm ";
		Cursor cursor = db.rawQuery(sql, new String[]{xian});
		while(cursor.moveToNext()){
			String zhan =  cursor.getString(cursor.getColumnIndex("zhan"));
			String pm =  cursor.getString(cursor.getColumnIndex("pm"));
			list.add(pm + zhan);
		}
		db.close();
		return list; 
 	}
 	public int getGoCounts(String databasePath,String xian){//根据线路查询站点数量（去程）
 		int counts = -1;
 		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
 		String sql = "SELECT max(pm) as count from cnbus,cnbusw where id = xid and busw = ? and cnbus.kind = 1;";
 		Cursor cursor = db.rawQuery(sql, new String[]{xian});
		while(cursor.moveToNext()){
			counts =  cursor.getInt(cursor.getColumnIndex("count"));
		}
		db.close();
 		return counts;
 	}
 	public static List<String> getZhanByXianBack(String databasePath,String xian){//根据线路查询站点（返程）
 		list = new ArrayList<String>();
		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
		String sql = "select cnbus.zhan,pm from cnbus,cnbusw where cnbus.xid  = cnbusw.id and cnbusw.busw = ? and cnbus.kind = 2 order by  pm ";
		Cursor cursor = db.rawQuery(sql, new String[]{xian});
		while(cursor.moveToNext()){
			String zhan=  cursor.getString(cursor.getColumnIndex("zhan"));
			String pm =  cursor.getString(cursor.getColumnIndex("pm"));
			list.add(pm + zhan);
		}
		db.close();
		return list; 		
 	}
// 	public static String getPmBack(){
// 		String pm = null;
// 		return pm;
// 		
// 	}
 	public int getBackCounts(String databasePath,String xian){//根据线路查询站点数量（返程）
 		int counts = -1;
 		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
 		String sql = "SELECT max(pm) as count from cnbus,cnbusw where id = xid and busw = ? and cnbus.kind = 2;";
		Cursor cursor = db.rawQuery(sql, new String[]{xian});
		while(cursor.moveToNext()){
			counts =  cursor.getInt(cursor.getColumnIndex("count"));
		}
		db.close();
 		return counts;
 	}
 	public static String getXianInfo(String databasePath,String xian){//根据线路查询线路信息
 		String info = "暂无信息";
 		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
 		Cursor cursor = db.query("cnbusw", new String[]{"shijian","kind","gjgs","piao"}, "busw = ?", new String[]{xian}, null, null, null);
		while(cursor.moveToNext()){
			String shijian = cursor.getString(cursor.getColumnIndex("shijian"));
			String kind = cursor.getString(cursor.getColumnIndex("kind"));
			String gjgs = cursor.getString(cursor.getColumnIndex("gjgs"));
			String piao = cursor.getString(cursor.getColumnIndex("piao"));
			info = xian + "\n" + shijian + "\n" + kind + "\n" + gjgs+ "\n" + piao;
		}
		db.close();
 		return info;
 	}
// 	public static int getZhanCount(String databasePath,String zhanStart,String zhanEnd,String xian){//根据出发站、终点站和线路查询间隔的站数
// 		int count = -1;
// 		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
// 		String sql = "select abs(A.pm - B.pm) as zhanCount from " +
// 				"(select pm from cnbus,cnbusw where cnbus.xid = cnbusw.id and cnbus.zhan = ? and busw = ? and cnbus.kind = 1)A," +
// 				"(select pm from cnbus,cnbusw where cnbus.xid = cnbusw.id and  cnbus.zhan = ? and busw = ? and cnbus.kind = 1)B";
// 		Cursor cursor = db.rawQuery(sql, new String[]{zhanStart,xian,zhanEnd,xian});
//		while(cursor.moveToNext()){
//			count = cursor.getInt(cursor.getColumnIndex("zhanCount"));
//		}
//		db.close();
// 		return count;		
// 	}+
 	public static int getZhanCount(String databasePath,String zhanStart,String zhanEnd,String xian){//根据出发站、终点站和线路查询间隔的站数
 		int pm = -1;
 		int kind = -1;
 		int count = -1;
 		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
 		String sql = "select abs(A.pm - B.pm) as zhanCount from " +
 				"(select pm from cnbus,cnbusw where cnbus.xid = cnbusw.id and cnbus.zhan = ? and busw = ? and cnbus.kind = ?)A," +
 				"(select pm from cnbus,cnbusw where cnbus.xid = cnbusw.id and  cnbus.zhan = ? and busw = ? and cnbus.kind = ?)B";
 		String pmSql = "select min(pm) as pm from cnbus,cnbusw where cnbus.xid  = cnbusw.id and cnbusw.busw = ? and zhan= ? ";
 		Cursor pmCursor = db.rawQuery(pmSql, new String[]{xian,zhanStart});
		while(pmCursor.moveToNext()){
			pm = pmCursor.getInt(pmCursor.getColumnIndex("pm"));
		}
		String kindSql = "select cnbus.kind  from cnbus,cnbusw where cnbus.xid  = cnbusw.id" +
				" and cnbusw.busw = ? and zhan= ? and pm = ?";
		Cursor kindCursor = db.rawQuery(kindSql, new String[]{xian,zhanStart,pm+""});
		while(kindCursor.moveToNext()){
			kind = kindCursor.getInt(kindCursor.getColumnIndex("kind"));
		}
 		Cursor cursor = db.rawQuery(sql, new String[]{zhanStart,xian,kind+"",zhanEnd,xian,kind+""});
		while(cursor.moveToNext()){
			count = cursor.getInt(cursor.getColumnIndex("zhanCount"));
		}
		db.close();
 		return count;		
 	}
 	public static List<String> getDChanged(String databasePath,String zhanStart,String zhanEnd){//根据出发站、终点站查询换乘的线路（直达）
 		list = new ArrayList<String>();
		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
		String sql = "select  DISTINCT A.busw as busw from  " +
				"(select  busw from cnbus,cnbusw where cnbus.xid = cnbusw.id and  cnbus.zhan = ? order by  shuzi )A," +
				"(select  busw from cnbus,cnbusw where cnbus.xid = cnbusw.id and  cnbus.zhan = ? order by  shuzi )B " +
				"where A.busw = B.busw ;";
		Cursor cursor = db.rawQuery(sql, new String[]{zhanStart,zhanEnd});
		while(cursor.moveToNext()){
			String busw=  cursor.getString(cursor.getColumnIndex("busw"));
			list.add(busw);
		}
		db.close();
 		return list;
 	}
 	public static List<String> getChangedZhan(String databasePath,String zhanStart,String zhanEnd){//根据出发站、终点站获取换成的站点
 		list = new ArrayList<String>();
		db = SQLiteDatabase.openOrCreateDatabase(databasePath, null);
		String sql = "select A.zhan from " + 
				"(select DISTINCT zhan from cnbus,cnbusw where busw in (select DISTINCT cnbusw.busw from cnbus,cnbusw where cnbus.xid = cnbusw.id " +
				"and cnbus.zhan = ? order by  shuzi) and xid = id)A," +
				"(select DISTINCT zhan from cnbus,cnbusw where busw in (select DISTINCT cnbusw.busw from cnbus,cnbusw where cnbus.xid = cnbusw.id "+
				"and cnbus.zhan = ? order by  shuzi) and xid = id)B "+
				"where A.zhan = B.zhan;";
		Cursor cursor = db.rawQuery(sql, new String[]{zhanStart,zhanEnd});
		while(cursor.moveToNext()){
			String zhan=  cursor.getString(cursor.getColumnIndex("zhan"));
			list.add(zhan);
		}
		db.close();
 		return list;
 	}
 	

 	

}
